#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *
	
def Citi_Monthly_Position(ref_date):

	hld = query_hld(ref_date, portf_list=['SC028'])
	
	tplt_file = TPLT_PATH + 'Springs Capital Position File.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Position')
	
	xSht.Cells(2, 1).Value = 'As at ' + ref_date.strftime("%d %B %Y") 
	
	hk_stock_row = 5
	us_stock_row = 100
	futures_row = 200
	pnote_row = 300
	
	for elem in hld:
		if elem[2] == 'CASH' or elem[2] == 'REPO':
			continue
		elif elem[2] == 'PNOTE':
			blp_ticker = ch_ticker_to_blp_ticker(elem[0])
			sec_name = query_blp_static([blp_ticker], ['NAME'])[blp_ticker]['NAME']
			underlying_price = query_latest_price(ref_date, elem[0])
			underlying_cnrncy = 'CNY'
			if elem[0][0] == '2':
				underlying_cnrncy = 'HKD'
			if elem[0][0] == '9':
				underlying_cnrncy = 'USD'
				
			xSht.Cells(pnote_row, 1).Value = sec_name
			xSht.Cells(pnote_row, 2).Value = 'PN'
			xSht.Cells(pnote_row, 4).Value = blp_ticker
			xSht.Cells(pnote_row, 5).Value = 'L' if elem[3]=='LONG' else 'S'
			xSht.Cells(pnote_row, 6).Value = elem[4]
			xSht.Cells(pnote_row, 7).Value = underlying_cnrncy
			xSht.Cells(pnote_row, 8).Value = underlying_price
			xSht.Cells(pnote_row, 9).Value = elem[17]
			xSht.Cells(pnote_row, 10).Value = elem[7]
			xSht.Cells(pnote_row, 11).Value = elem[4] * underlying_price
			xSht.Cells(pnote_row, 12).Value = elem[9]
			
			pnote_row += 1			
			
		elif (elem[2] == 'STOCK' and elem[17] == 'HKD'):
			blp_ticker = hk_ticker_to_blp_ticker(elem[0])
			sec_name = query_blp_static([blp_ticker], ['NAME'])[blp_ticker]['NAME']
			
			xSht.Cells(hk_stock_row, 1).Value = sec_name
			xSht.Cells(hk_stock_row, 2).Value = 'EQ'
			xSht.Cells(hk_stock_row, 3).Value = blp_ticker
			xSht.Cells(hk_stock_row, 5).Value = 'L' if elem[3]=='LONG' else 'S'
			xSht.Cells(hk_stock_row, 6).Value = elem[4]
			xSht.Cells(hk_stock_row, 7).Value = elem[17]
			xSht.Cells(hk_stock_row, 10).Value = elem[7]
			xSht.Cells(hk_stock_row, 11).Value = elem[9]
			
			hk_stock_row += 1
		
		elif (elem[2] == 'STOCK' and elem[17] == 'USD'):
			blp_ticker = elem[0]
			sec_name = query_blp_static([blp_ticker], ['NAME'])[blp_ticker]['NAME']
			
			xSht.Cells(us_stock_row, 1).Value = sec_name
			xSht.Cells(us_stock_row, 2).Value = 'EQ'
			xSht.Cells(us_stock_row, 3).Value = blp_ticker
			xSht.Cells(us_stock_row, 5).Value = 'L' if elem[3]=='LONG' else 'S'
			xSht.Cells(us_stock_row, 6).Value = elem[4]
			xSht.Cells(us_stock_row, 7).Value = elem[17]
			xSht.Cells(us_stock_row, 10).Value = elem[7]
			xSht.Cells(us_stock_row, 11).Value = elem[9]
			
			us_stock_row += 1
			
		else: #Futures
			blp_ticker = elem[0]
			sec_name = query_blp_static([blp_ticker], ['NAME'])[blp_ticker]['NAME']
			
			xSht.Cells(futures_row, 1).Value = sec_name
			xSht.Cells(futures_row, 2).Value = 'FT'
			xSht.Cells(futures_row, 3).Value = blp_ticker
			xSht.Cells(futures_row, 5).Value = 'L' if elem[3]=='LONG' else 'S'
			xSht.Cells(futures_row, 6).Value = elem[4]
			xSht.Cells(futures_row, 7).Value = elem[17]
			xSht.Cells(futures_row, 10).Value = elem[7]
			xSht.Cells(futures_row, 11).Value = elem[9]
			
			futures_row += 1			
		
	if hk_stock_row > 5:
		xSht.Cells(hk_stock_row, 1).Value = 'Total'
		xSht.Cells(hk_stock_row, 6).Formula = '=SUM(F5:F' + str(hk_stock_row - 1) + ')'
		xSht.Cells(hk_stock_row, 11).Formula = '=SUM(K5:K' + str(hk_stock_row - 1) + ')'
		xSht.Rows(hk_stock_row).Font.Bold = True
		
	if us_stock_row > 100:
		xSht.Cells(us_stock_row, 1).Value = 'Total'
		xSht.Cells(us_stock_row, 6).Formula = '=SUM(F100:F' + str(us_stock_row - 1) + ')'
		xSht.Cells(us_stock_row, 11).Formula = '=SUM(K100:K' + str(us_stock_row - 1) + ')'
		xSht.Rows(us_stock_row).Font.Bold = True
		
	if futures_row > 200:
		xSht.Cells(futures_row, 1).Value = 'Total'
		xSht.Cells(futures_row, 6).Formula = '=SUM(F200:F' + str(futures_row - 1) + ')'
		xSht.Cells(futures_row, 11).Formula = '=SUM(K200:K' + str(futures_row - 1) + ')'
		xSht.Rows(futures_row).Font.Bold = True
		
	if pnote_row > 300:
		xSht.Cells(pnote_row, 1).Value = 'Total'
		xSht.Cells(pnote_row, 6).Formula = '=SUM(F300:F' + str(pnote_row - 1) + ')'
		xSht.Cells(pnote_row, 11).Formula = '=SUM(K300:K' + str(pnote_row - 1) + ')'
		xSht.Cells(pnote_row, 12).Formula = '=SUM(L300:L' + str(pnote_row - 1) + ')'
		xSht.Rows(pnote_row).Font.Bold = True
	
	xSht.Rows(str(hk_stock_row + 2) + ':99').Hidden = True
	xSht.Rows(str(us_stock_row + 2) + ':199').Hidden = True
	xSht.Rows(str(futures_row + 2) + ':299').Hidden = True
		
	rept_file = REPT_PATH + 'Springs Capital Position File ' + ref_date.strftime("%Y%m%d") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp
	
	print 'info, citi monthly position report completed at ', ref_date.isoformat()